<?php
/**
 * Created by PhpStorm.
 * Script Name: Database.php
 * Create: 6/8/23 10:22 PM
 * Description:
 * Author: fudaoji<fdj@kuryun.cn>
 */

namespace addons\helper\common\service;
use think\facade\Db as ThinkDb;
use think\migration\db\Column;

class Database
{
    const INNODB = 'InnoDB';
    const MYISAM = 'MyISAM';

    /**
     * 获取表{name:name}
     * Author: fudaoji<fdj@kuryun.cn>
     */
    static function getTableNames(){
        $tables = self::getTables();
        $list = [];
        foreach ($tables as $table){
            $list[$table['TABLE_NAME']] = $table['TABLE_NAME'];
        }
        return $list;
    }

    static function getTables($database = ''){
        empty($database) && $database = self::getDatabaseName();
        return ThinkDb::query("SELECT * FROM  information_schema.`TABLES` WHERE  TABLE_SCHEMA='$database'");
    }

    /**
     * 修复表
     * @param $tables
     * @return bool|string
     * Author: fudaoji<fdj@kuryun.cn>
     */
    static function repair($tables){
        if(empty($tables)){
            return "请传入要修复的表";
        }
        try {
            if(is_string($tables)){
                $tables = explode(',', $tables);
            }
            $tables = implode('`,`', $tables);
            ThinkDb::query("REPAIR TABLE `{$tables}`");
            return true;
        }catch (\Exception $e){
            return $e->getMessage();
        }
    }

    /**
     * 表优化
     * @param $tables
     * @return bool|string
     * Author: fudaoji<fdj@kuryun.cn>
     */
    static function optimize($tables){
        if(empty($tables)){
            return "请传入要优化的表";
        }
        try {
            if(is_string($tables)){
                $tables = explode(',', $tables);
            }
            $tables = implode('`,`', $tables);
            ThinkDb::query("OPTIMIZE TABLE `{$tables}`");
            return true;
        }catch (\Exception $e){
            return $e->getMessage();
        }
    }

    /**
     * 索引类型
     * @return array
     * Author: fudaoji<fdj@kuryun.cn>
     */
    static function indexMethods()
    {
        return  [
            'BTREE' => 'BTREE',
            'HASH' => 'HASH',
        ];
    }

    /**
     * 索引类型
     * @return array
     * Author: fudaoji<fdj@kuryun.cn>
     */
    static function indexTypes()
    {
        return  [
            'normal' => 'NORMAL',
            'unique' => 'UNIQUE',
            'fulltext' => 'FULLTEXT',
            'spatial' => 'SPATIAL',
        ];
    }

    /**
     * 字段类型
     * @return array
     * Author: fudaoji<fdj@kuryun.cn>
     */
    static function columnTypes()
    {
        return  [
            'int' => 'int',
            'tinyint' => 'tinyint',
            'smallint' => 'smallint',
            'mediumint' => 'mediumint',
            'bigint' => 'bigint',

            'char' => 'char',
            'varchar' => 'varchar',
            //no len
            'tinytext' => 'tinytext',
            'text' => 'text',
            'mediumtext' => 'mediumtext',
            'longtext' => 'longtext',
            'tinyblob' => 'tinyblob',
            'blob' => 'blob',
            'mediumblob' => 'mediumblob',
            'longblob' => 'longblob',
            //no len end

            'date' => 'date', //no len
            'datetime' => 'datetime',
            'time' => 'time',
            'timestamp' => 'timestamp',

            'enum' => 'enum', //no len
            'float' => 'float', //(len, decimal)
            'decimal' => 'decimal', //(len, decimal)
            'double' => 'double', //(len, decimal)

            'binary' => 'binary', //len
            'varbinary' => 'varbinary', //len
            'bit' => 'bit', //len
        ];
    }

    /**
     * 存储引擎
     * @param null $id
     * @return array|mixed
     * Author: fudaoji<fdj@kuryun.cn>
     */
    public static function engines($id = null){
        $list = [
            self::INNODB => 'InnoDB',
            self::MYISAM => 'MyISAM'
        ];
        return isset($list[$id]) ? $list[$id] : ($id === null ? $list : $list[0]);
    }

    static function getDatabaseName(){
        return config('database.connections')[config('database.default')]['database'];
    }

    static function getTablePrefix(){
        return config('database.connections')[config('database.default')]['prefix'];
    }

    /**
     * 验证column是否存在
     * @param string $table
     * @param string $column
     * @return bool
     * @throws \think\db\exception\BindParamException
     * Author: fudaoji<fdj@kuryun.cn>
     */
    static function checkColumnExist($table = '', $column = ''){
        return count(self::getColumn($table, $column)) ? true : false;
    }

    /**
     * 获取column
     * @param string $table
     * @param string $column
     * @return array
     * @throws \think\db\exception\BindParamException
     * Author: fudaoji<fdj@kuryun.cn>
     */
    static function getColumn($table = '', $column = ''){
        $database = self::getDatabaseName();
        $list = ThinkDb::query("select * from information_schema.COLUMNS where TABLE_SCHEMA = '$database' and table_name = '$table' and COLUMN_NAME='{$column}'");
        if(count($list)){
            return self::columnInfoMap($list[0]);
        }
        return [];
    }

    /**
     * 获取字段hash {name:name, ...}
     * @param string $table
     * @param string $database
     * @return array|false
     * @throws \think\db\exception\BindParamException
     * Author: fudaoji<fdj@kuryun.cn>
     */
    static function getColumnHash($table = '', $database = ''){
        empty($database) && $database = self::getDatabaseName();
        $res = ThinkDb::query("select COLUMN_NAME from information_schema.COLUMNS where TABLE_SCHEMA = '$database' and table_name = '$table'");
        $arr = [];
        foreach ($res as $item){
            $arr[$item['COLUMN_NAME']] = $item['COLUMN_NAME'];
        }
        return  $arr;
    }

    /**
     * 获取索引
     * @param string $table
     * @param string $index
     * @return array|mixed
     * Author: fudaoji<fdj@kuryun.cn>
     */
    static function getIndex($table = '', $index = ''){
        $list = ThinkDb::query("SHOW INDEX FROM $table where  Key_name='{$index}'");
        if($list){
            $keys = [];
            foreach ($list as $index){
                $key_name = $index['Key_name'];
                if (!isset($keys[$key_name])) {
                    $keys[$key_name] = self::indexInfoMap($index);
                }
                !in_array($index['Column_name'], $keys[$key_name]['columns']) && $keys[$key_name]['columns'][] = $index['Column_name'];
            }
            return array_values($keys)[0];
        }
        return [];
    }

    /**
     * 删除字段
     * @param string $table_name
     * @param string $column
     * @return bool|string
     * Author: fudaoji<fdj@kuryun.cn>
     */
    public static function dropColumn($table_name = '', $column = ''){
        try{
            $sql = "ALTER TABLE `{$table_name}` DROP COLUMN `{$column}`";
            ThinkDb::query($sql);
            return true;
        }catch (\Exception $e){
            return $e->getMessage();
        }
    }

    /**
     * 删除索引
     * @param $table_name
     * @param $index
     * @return bool|string
     * Author: fudaoji<fdj@kuryun.cn>
     */
    public static function dropIndex($table_name, $index)
    {
        try{
            $sql = "ALTER TABLE `{$table_name}` DROP INDEX `{$index}`";
            ThinkDb::query($sql);
            return true;
        }catch (\Exception $e){
            return $e->getMessage();
        }
    }

    /**
     * 删除表
     * @param string $table_name
     * @return bool|string
     * Author: fudaoji<fdj@kuryun.cn>
     */
    public static function dropTable($table_name = '')
    {
        try{
            $sql = "DROP TABLE IF EXISTS  `{$table_name}`";
            ThinkDb::query($sql);
            return true;
        }catch (\Exception $e){
            return $e->getMessage();
        }
    }


    /**
     * 获取字段长度
     *
     * @param $schema
     * @return string
     */
    static function getLengthValue($schema)
    {
        $type = $schema['DATA_TYPE'];
        switch ($type){
            case 'int':
            case 'bigint':
            case 'smallint':
            case 'mediumint':
            case 'float':
            case 'decimal':
            case 'double':
                return $schema['NUMERIC_PRECISION'];
            case 'time':
            case 'datetime':
            case 'timestamp':
                return $schema['CHARACTER_MAXIMUM_LENGTH'];
            case 'varchar':
            case 'text':
            case 'char':
                return $schema['CHARACTER_MAXIMUM_LENGTH'];
            case 'enum':
                return implode(',', array_map(function($item){
                    return trim($item, "'");
                }, explode(',', substr($schema['COLUMN_TYPE'], 5, -1))));
        }
        return '';
    }

    /**
     * 字段信息整理
     * @param array $item
     * @return array
     * Author: fudaoji<fdj@kuryun.cn>
     */
    static function columnInfoMap($item = []){
        $field = $item['COLUMN_NAME'];
        $unsigned = 0;
        $zerofill = 0;
        $decimal = 0;
        if(strpos($item['COLUMN_TYPE'], 'unsigned') !== false){
            $unsigned = 1;
        }
        if(strpos($item['COLUMN_TYPE'], 'zerofill') !== false){
            $zerofill = 1;
        }
        if(!empty($item['NUMERIC_SCALE'])){
            $decimal = $item['NUMERIC_SCALE'];
        }
        $type = explode(' ', $item['COLUMN_TYPE'])[0];
        $type = explode('(', $type)[0];

        return  [
            'field' => $field,
            'type' => $type,
            'comment' => $item['COLUMN_COMMENT'],
            'default' => $item['COLUMN_DEFAULT'],
            'length' => self::getLengthValue($item),
            'nullable' => intval($item['IS_NULLABLE'] !== 'NO'),
            'primary_key' => intval($item['COLUMN_KEY'] === 'PRI'),
            'auto_increment' => intval(strpos($item['EXTRA'], 'auto_increment') !== false),
            'unsigned' => $unsigned,
            'zerofill' => $zerofill,
            'decimal' => $decimal
        ];
    }

    /**
     * 表结构信息
     * @param $table
     * @param null $section
     * @return array|mixed
     * Author: fudaoji<fdj@kuryun.cn>
     */
    static function getSchema($table, $section = null)
    {
        $database = self::getDatabaseName();
        $schema_raw = $section !== 'table' ? ThinkDb::query("select * from information_schema.COLUMNS where TABLE_SCHEMA = '$database' and table_name = '$table'") : [];
        $columns = [];
        foreach ($schema_raw as $item) {
            $columns[] = self::columnInfoMap($item);
        }
        $table_schema = $section == 'table' || !$section ? ThinkDb::query("SELECT * FROM  information_schema.`TABLES` WHERE  TABLE_SCHEMA='$database' and TABLE_NAME='$table'") : [];
        $indexes = $section == 'keys' || !$section ? ThinkDb::query("SHOW INDEX FROM $table") : [];
        $keys = [];

        foreach ($indexes as $index) {
            $key_name = $index['Key_name'];
            if ($key_name == 'PRIMARY') {
                continue;
            }
            if (!isset($keys[$key_name])) {
                $keys[$key_name] = self::indexInfoMap($index);
            }
            !in_array($index['Column_name'], $keys[$key_name]['columns']) && $keys[$key_name]['columns'][] = $index['Column_name'];
        }
        $data = [
            'table' => !empty($table_schema[0]) ? self::tableInfoMap($table_schema[0]) : [],
            'columns' => $columns,
            'keys' => array_values($keys)
        ];
        return $section ? $data[$section] : $data;
    }

    /**
     * 索引信息整理
     * @param array $item
     * @return array
     * Author: fudaoji<fdj@kuryun.cn>
     */
    static function indexInfoMap($item = []){
        return  [
            'name' => $item["Key_name"],
            'columns' => [$item["Column_name"]],
            'type' => $item['Non_unique'] == 0 ? 'unique' : 'normal',
            'method' => $item['Index_type'],
            'comment' => $item["Index_comment"]
        ];
    }

    /**
     * 表格信息整理
     * @param array $old
     * @return array
     * Author: fudaoji<fdj@kuryun.cn>
     */
    static function tableInfoMap($old = []){
        $new = [];
        $map = [
            "TABLE_CATALOG" => "table_catalog",
            "TABLE_SCHEMA" => "table_schema",
            "TABLE_NAME" => "table_name",
            "TABLE_TYPE" => "table_type",
            "ENGINE" => "engine",
            "VERSION" => "version",
            "ROW_FORMAT" => "row_format",
            "TABLE_ROWS" => "table_rows",
            "AVG_ROW_LENGTH" => "avg_row_length",
            "DATA_LENGTH" => "data_length",
            "MAX_DATA_LENGTH" => "max_data_length",
            "INDEX_LENGTH" => "index_length",
            "DATA_FREE" => "data_free",
            "AUTO_INCREMENT" => "auto_increment",
            "CREATE_TIME" => "create_time",
            "UPDATE_TIME" => "update_time",
            "CHECK_TIME" => "check_time",
            "TABLE_COLLATION" => "table_collation",
            "CHECKSUM" => "checksum",
            "CREATE_OPTIONS" => "create_options",
            "TABLE_COMMENT" => "table_comment",
        ];
        foreach ($map as $k => $v){
            isset($old[$k]) && $new[$v] = $old[$k];
        }
        return $new;
    }

}